package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonInfoDao;
import com.boss.salary.entity.PersonExt;
import com.boss.salary.entity.PersonInfo;
import com.boss.salary.vo.PersonInfoVO;
import com.boss.salary.vo.UnitSalaryReportVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class PersonInfoDaoImpl extends BaseDaoImpl implements IPersonInfoDao {

    @Override
    public List<PersonInfo> queryPersonInfoPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT a.id, a.create_time, a.update_time, a.person_name, a.sex_code, a.iden_type_code,")
                .append(" a.iden_no, a.nation, a.nat_code, a.state, a.birthday, a.work_init_date, a.iden_is_verify,")
                .append(" a.fundmode, a.is_deleted, a.mof_div_code, b.person_address, b.agency_id, b.dept_id, b.agency_code,")
                .append(" b.enter_agency_date,b.retire_date, b.person_type_code, b.person_ide_code, b.end_date,")
                .append(" b.dead_date, b.remarks, b.sala_card_no, b.sala_card_bank, b.gov_cred_card_no,")
                .append(" b.gov_cred_card_bank, b.start_date, b.merit_pay_code, b.sch_rec_code,")
                .append(" b.pos_code, b.gr_code, b.tec_gr_code, b.is_auth, b.sala_gr_code, b.person_state,")
                .append(" b.change_state, b.per_source_code, b.is_special_post, b.is_insurance,b.personnel_code, is_stop_salary,")
                .append("b.is_uni_sala,b.show_order,c.agency_name,d.dept_name, d.dept_code")
                .append(" FROM ").append(TableName.PERSON_INFO).append(" a")
                .append(" join ").append(TableName.PERSON_EXT).append(" b")
                .append(" join ").append(TableName.AGENCY_INFO).append(" c")
                .append(" on a.id = b.person_id")
                .append(" and b.agency_id = c.id")
                .append(" left join ").append(TableName.DEPARTMENT_INFO).append(" d")
                .append(" on b.dept_id = d.id")
                .append(" where a.is_deleted = ? ");

        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and b.agency_id = ? ");
        paramList.add(queryParam.get("agencyId"));

        if (!StringUtils.isEmpty(queryParam)) {

            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                String flag = queryParam.get("flag").toString();
                if (!"all".equals(flag)) {
                    sql.append(" and b.verify_state = ?");
                    paramList.add(flag);
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("meritPayCode"))) {
                String meritPayCode = queryParam.get("meritPayCode").toString();
                sql.append(" and b.merit_pay_code = ").append(meritPayCode);
                paramList.add(meritPayCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("posCode"))) {
                String posCode = queryParam.get("posCode").toString();
                sql.append(" and b.pos_code = ").append(posCode);
                paramList.add(posCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("grCode"))) {
                String grCode = queryParam.get("grCode").toString();
                sql.append(" and b.gr_code = ").append(grCode);
                paramList.add(grCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("personnelCode"))) {
                String personnelCode = queryParam.get("personnelCode").toString();
                sql.append(" and b.personnel_code like concat( '%', ?, '%')");
                paramList.add(personnelCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                String personName = queryParam.get("personName").toString();
                sql.append(" and a.person_name like concat( '%', ?, '%')");
                paramList.add(personName);
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                String idenNo = queryParam.get("idenNo").toString();
                sql.append(" and a.iden_no like concat( '%', ?, '%')");
                paramList.add(idenNo);
            }
            if(!StringUtils.isEmpty(queryParam.get("state"))){
                sql.append(" and a.state=?");
                paramList.add(queryParam.get("state"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and a.mof_div_code=?");
                paramList.add(queryParam.get("mofDivCode"));
            }

        }
        sql.append(" ORDER BY d.dept_code,-b.show_order desc,b.person_name,a.id ");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(PersonInfo.class), params);
    }



    @Override
    public int queryPersonInfoTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(1) FROM ").append(TableName.PERSON_INFO).append(" a")
                .append(" join ").append(TableName.PERSON_EXT).append(" b")
                .append(" on a.id = b.person_id")
                .append(" where a.is_deleted = ?");
        paramList.add(SystemDictConstant.IS_FALSE);
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("agencyId"))) {
                sql.append(" and b.agency_id = ? ");
                paramList.add(queryParam.get("agencyId"));
            }
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                String flag = queryParam.get("flag").toString();
                if (!"all".equals(flag)) {
                    sql.append(" and b.verify_state = ?");
                    paramList.add(flag);
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("meritPayCode"))) {
                String meritPayCode = queryParam.get("meritPayCode").toString();
                sql.append(" and b.merit_pay_code = ").append(meritPayCode);
                paramList.add(meritPayCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("posCode"))) {
                String posCode = queryParam.get("posCode").toString();
                sql.append(" and b.pos_code = ").append(posCode);
                paramList.add(posCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("grCode"))) {
                String grCode = queryParam.get("grCode").toString();
                sql.append(" and b.gr_code = ").append(grCode);
                paramList.add(grCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("personnelCode"))) {
                String personnelCode = queryParam.get("personnelCode").toString();
                sql.append(" and b.personnel_code = ?");
                paramList.add(personnelCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                String personName = queryParam.get("personName").toString();
                sql.append(" and a.person_name like concat( '%', ?, '%')");
                paramList.add(personName);
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                String idenNo = queryParam.get("idenNo").toString();
                sql.append(" and a.iden_no like concat( '%', ?, '%')");
                paramList.add(idenNo);
            }
            if(!StringUtils.isEmpty(queryParam.get("state"))){
                sql.append(" and a.state=?");
                paramList.add(queryParam.get("state"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and a.mof_div_code=?");
                paramList.add(queryParam.get("mofDivCode"));
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    @Override
    public List<PersonInfo> queryStopPaySalaryPage(PaginationDTO paginationDTO, Map<String, Object> queryParam,List<String> agencyList) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT a.*, b.id extId, b.person_address, b.agency_id, b.dept_id, b.agency_code, b.enter_agency_date,")
                .append(" b.retire_date, b.person_type_code, b.person_ide_code, b.end_date,")
                .append(" b.dead_date, b.remarks, b.sala_card_no, b.sala_card_bank, b.gov_cred_card_no,")
                .append(" b.gov_cred_card_bank, b.start_date, b.merit_pay_code, b.sch_rec_code,")
                .append(" b.pos_code, b.gr_code, b.tec_gr_code, b.is_auth, b.sala_gr_code, b.person_state,")
                .append(" b.change_state, b.per_source_code, b.is_special_post, b.is_insurance, b.is_uni_sala, b.is_stop_salary")
                .append(" FROM ").append(TableName.PERSON_INFO).append(" a")
                .append(" join ").append(TableName.PERSON_EXT).append(" b")
                .append(" on a.id = b.person_id")
                .append(" where a.is_deleted = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and a.mof_div_code= ? ");
        paramList.add(queryParam.get("province"));
        //isEmpty(queryParam)判断queryParam是空  !StringUtils.isEmpty(queryParam)判断queryParam是非空
        if (!StringUtils.isEmpty(queryParam)) {
            if (!CollectionUtils.isEmpty(agencyList)) {
                sql.append(" and ").append(SysUtils.inSql(" b.agency_id ",agencyList));
            }
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                String flag = queryParam.get("flag").toString();
                if (!"all".equals(flag)) {
                    sql.append(" and b.is_stop_salary = ?");
                    paramList.add(flag);
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("meritPayCode"))) {
                String meritPayCode = queryParam.get("meritPayCode").toString();
                sql.append(" and b.merit_pay_code = ?");
                paramList.add(meritPayCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("posCode"))) {
                String posCode = queryParam.get("posCode").toString();
                sql.append(" and b.pos_code = ?");
                paramList.add(posCode);
            }
            if (!StringUtils.isEmpty(queryParam.get("grCode"))) {
                String grCode = queryParam.get("grCode").toString();
                sql.append(" and b.gr_code = ?");
                paramList.add(grCode);
            }

            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                String personName = queryParam.get("personName").toString();
                sql.append(" and a.person_name like concat( '%', ?, '%')");
                paramList.add(personName);
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                String idenNo = queryParam.get("idenNo").toString();
                sql.append(" and a.iden_no like concat( '%', ?, '%')");
                paramList.add(idenNo);
            }
        }
        sql.append(" ORDER BY a.CREATE_TIME");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(PersonInfo.class), params);
    }


    @Override
    public int queryStopPaySalaryTotal(Map<String, Object> queryParam,List<String> agencyList) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(1) FROM ").append(TableName.PERSON_INFO).append(" a")
                .append(" join ").append(TableName.PERSON_EXT).append(" b")
                .append(" on a.id = b.person_id")
                .append(" where a.is_deleted = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and a.mof_div_code= ? ");
        paramList.add(queryParam.get("province"));
        if (!StringUtils.isEmpty(queryParam)) {
            if (!CollectionUtils.isEmpty(agencyList)) {
                sql.append(" and ").append(SysUtils.inSql(" b.agency_id ",agencyList));
            }
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                String flag = queryParam.get("flag").toString();
                sql.append(" and b.is_stop_salary = ?");
                paramList.add(flag);
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                String personName = queryParam.get("personName").toString();
                sql.append(" and a.person_name like concat( '%', ?, '%')");
                paramList.add(personName);
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                String idenNo = queryParam.get("idenNo").toString();
                sql.append(" and a.iden_no like concat( '%', ?, '%')");
                paramList.add(idenNo);
            }
        }

        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,paramList.toArray());
    }

    @Override
    public void savePersonInfo(PersonInfo personInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(TableName.PERSON_INFO);
        sql.append("(id, create_time, update_time, person_name, sex_code, iden_type_code, iden_no, ")
                .append("nation, nat_code, state, birthday, work_init_date, iden_is_verify, ")
                .append("fundmode, is_deleted,mof_div_code )");
        sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                personInfo.getId(), personInfo.getCreateTime(), personInfo.getUpdateTime(),
                personInfo.getPersonName(), personInfo.getSexCode(), personInfo.getIdenTypeCode(),
                personInfo.getIdenNo(), personInfo.getNation(), personInfo.getNatCode(), personInfo.getState(),
                personInfo.getBirthday(), personInfo.getWorkInitDate(), personInfo.getIdenIsVerify(),
                personInfo.getFundmode(), personInfo.getIsDeleted(),personInfo.getMofDivCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updatePersonInfo(PersonInfo personInfo) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_INFO).append(" set ");
        sql.append(" update_time = ? ");
        paramList.add(personInfo.getUpdateTime());
        if (!StringUtils.isEmpty(personInfo.getPersonName())) {
            sql.append(", person_name = ? ");
            paramList.add(personInfo.getPersonName().trim());
        }
        if (!StringUtils.isEmpty(personInfo.getSexCode())) {
            sql.append(", sex_code = ? ");
            paramList.add(personInfo.getSexCode());
        }
        if (!StringUtils.isEmpty(personInfo.getIdenTypeCode())) {
            sql.append(", iden_type_code = ? ");
            paramList.add(personInfo.getIdenTypeCode());
        }
        if (!StringUtils.isEmpty(personInfo.getIdenNo())) {
            sql.append(", iden_no = ? ");
            paramList.add(personInfo.getIdenNo());
        }
        if (!StringUtils.isEmpty(personInfo.getNation())) {
            sql.append(", nation = ? ");
            paramList.add(personInfo.getNation());
        }
        if (!StringUtils.isEmpty(personInfo.getNatCode())) {
            sql.append(", nat_code = ? ");
            paramList.add(personInfo.getNatCode());
        }
        if (!StringUtils.isEmpty(personInfo.getState())) {
            sql.append(", state = ? ");
            paramList.add(personInfo.getState());
        }
        if (!StringUtils.isEmpty(personInfo.getBirthday())) {
            sql.append(", birthday = ? ");
            paramList.add(personInfo.getBirthday());
        }
        if (!StringUtils.isEmpty(personInfo.getWorkInitDate())) {
            sql.append(", work_init_date = ? ");
            paramList.add(personInfo.getWorkInitDate());
        }
        if (!StringUtils.isEmpty(personInfo.getIdenIsVerify())) {
            sql.append(", iden_is_verify = ? ");
            paramList.add(personInfo.getIdenIsVerify());
        }
        if (!StringUtils.isEmpty(personInfo.getFundmode())) {
            sql.append(", fundmode = ? ");
            paramList.add(personInfo.getFundmode());
        }
        if (!StringUtils.isEmpty(personInfo.getIsDeleted())) {
            sql.append(", is_deleted = ? ");
            paramList.add(personInfo.getIsDeleted());
        }
        sql.append(" where id = ? ");
        paramList.add(personInfo.getId());
        this.getJdbcTemplate().update(sql.toString(), paramList.toArray());
    }



    @Override
    public void deletePersonInfo(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_INFO);
        sql.append(" set is_deleted='1' ")
                .append("where id=?");
        Object[] params = new Object[]{
                id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public Map<String, Object> getStateNum(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sum(if(a.state = '3' and b.is_stop_salary='0', 1, 0)) as registerNum, sum(if(a.state = '1'  and b.is_stop_salary='0', 1, 0)) as quitNum, ")
                .append(" sum(if(b.is_stop_salary='1', 1, 0)) as stopNum ")
                .append(" from ").append(TableName.PERSON_INFO).append(" a")
                .append(" join ").append(TableName.PERSON_EXT).append(" b")
                .append(" on a.id = b.person_id");
        sql.append(" where a.is_deleted = ?")
                .append(" and b.agency_id = ? ");
        Object[] params = new Object[]{
                SystemDictConstant.NOT_DELETED, queryParam.get("agencyId")
        };
        return this.getJdbcTemplate().queryForMap(sql.toString(), params);
    }

    @Override
    public List<PersonInfo> getPersonInfoByAgencyIds(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" pi.id, pi.create_time, pi.update_time, pe.person_name, pi.sex_code, pi.iden_type_code,")
                .append(" pe.iden_no, pi.nation, pi.nat_code, pe.state, pi.birthday, pi.work_init_date, pi.iden_is_verify,")
                .append(" pi.fundmode, pe.is_deleted, pe.person_id, pe.person_address, pe.agency_id, pe.dept_id, pe.agency_code,")
                .append(" pe.enter_agency_date, pe.retire_date, pe.person_type_code, pe.person_ide_code, pe.end_date, pe.dead_date,")
                .append(" pe.remarks, pe.sala_card_no, pe.sala_card_bank, pe.gov_cred_card_no, pe.gov_cred_card_bank, pe.start_date,")
                .append(" pe.merit_pay_code, pe.sch_rec_code, pe.pos_code, pe.gr_code, pe.tec_gr_code, pe.is_auth, pe.sala_gr_code,")
                .append(" pe.person_state, pe.change_state, pe.verify_state, pe.iden_verify_state, pe.per_source_code, pe.is_special_post,")
                .append(" pe.is_insurance, pe.is_uni_sala, pe.is_stop_salary, pe.person_type_id, pe.post_id, pe.grade_id, pe.level_id,pe.person_name,show_order,pe.phone,pe.personnel_code,func_id,func_name,func_code ");
        sql.append(" FROM ");
        sql.append(TableName.PERSON_INFO).append(" as pi ");

        sql.append(" JOIN ").append(TableName.PERSON_EXT_SUBMIT).append(" as pe on pi.id = pe.person_id");
        sql.append(" where pe.agency_id=? ");
//        sql.append(" where ").append(com.boss.sys.utils.SysUtils.inSql("pe.agency_id", agencyIds));
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(PersonInfo.class),agencyId);
    }

    @Override
    public void batchSavePersonHistory(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.PERSON_HISTORY)
                .append(" (id, history_key, salary_month, create_time, update_time, person_id, person_address, agency_id,")
                .append(" dept_id, agency_code, enter_agency_date, retire_date, person_type_code, person_ide_code, ")
                .append(" end_date, dead_date, remarks, sala_card_no, sala_card_bank, gov_cred_card_no, gov_cred_card_bank, ")
                .append(" start_date, merit_pay_code, sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, ")
                .append(" person_state, change_state, per_source_code, is_special_post, is_insurance, is_uni_sala, ")
                .append(" is_stop_salary, is_deleted, mof_div_code, person_type_id, post_id, grade_id, level_id,person_name, iden_no,state,show_order,phone,personnel_code,func_id,func_name,func_code )")
                .append(" values (")
                .append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 51; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deletePersonHistory(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(TableName.PERSON_HISTORY);
        sql.append(" WHERE salary_month = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{month});
    }

    @Override
    public UnitSalaryReportVO selectSendSalaryPersonTotal(String agencyId, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" ifNull(count(*),0) personTotal, ")
                .append(" ifNull(sum(case when pio.state ='3' and pet.is_stop_salary='0' then 1 else 0 end),0)workTotal, ")
                .append(" ifNull(sum(case when pio.state ='1' and pet.is_stop_salary='0' then 1 else 0 end),0) retireTotal, ")
                .append(" ifNull(sum(case pet.is_stop_salary when '1' then 1 else 0 end),0) stopTotal ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" AND pet.agency_id = ? ")
                .append(" where ").append(" pio.is_deleted = 0 ");
        params.add(agencyId);
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

    @Override
    public UnitSalaryReportVO selectSubmitSendSalaryPersonTotal(String agencyId, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" ifNull(count(*),0) personTotal, ")
                .append(" ifNull(sum(case pet.state when '3' then 1 else 0 end),0) workTotal, ")
                .append(" ifNull(sum(case pet.state when '1' then 1 else 0 end),0) retireTotal ")
                .append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" where ").append(" pet.is_stop_salary = 0 ")
                .append(" and pet.agency_id = ? ");
        params.add(agencyId);
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

    @Override
    public void batchUpdateBank(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe")
                .append(" on pi.id = pe.person_id ");
        sql.append(" set pe.sala_card_bank = ? , pe.sala_card_no = ? , pe.update_time = ? ");
        sql.append(" where pi.iden_no = ? and pi.is_deleted=0 and pe.agency_id=? ");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 5; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public String getPersonInfoById(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("select  person_name from ").append(TableName.PERSON_OPERATE)
                .append(" where person_id=? and is_deleted=?");
        sql.append(" order by update_time desc limit 1");
        Object[] params = new Object[]{
                id, SystemDictConstant.IS_FALSE
        };
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), String.class, params);
        } catch (Exception e) {
            return "";
        }
    }

    @Override
    public List<PersonInfo> getAllPersonInfo(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select spi.person_name,spi.iden_no,spe.sala_card_bank,spe.sala_card_no")
                .append(" from ").append(TableName.PERSON_INFO).append(" as spi")
                .append(" join ").append(TableName.PERSON_EXT).append(" as spe on spe.person_id=spi.id")
                .append(" where spi.is_deleted=? and spe.agency_id=? ");
        Object[] params = new Object[]{
                SystemDictConstant.IS_FALSE, agencyId
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonInfo.class), params);
    }

    @Override
    public PersonInfoVO selectOnePersonInfo(String personId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" pio.id, pio.state, ai.agency_code, ai.agency_name, pet.merit_pay_code, pio.person_name, ")
                .append(" pio.sex_code, pio.nat_code, pio.iden_type_code, pio.iden_no, ")
                .append(" pio.birthday, pet.sch_rec_code, pet.person_type_code, pet.person_address, ")
                .append(" pet.is_auth, pio.work_init_date, pet.enter_agency_date, pet.pos_code, ")
                .append(" pet.gr_code, pet.tec_gr_code, pet.sala_gr_code, pet.sala_card_no, ")
                .append(" pet.sala_card_bank, pio.nation, pet.retire_date, pet.person_ide_code, ")
                .append(" pio.fundmode, pet.is_special_post,pet.func_name,pet.phone, ")
                .append(" pet.person_type_id, pet.post_id,pet.grade_id,pet.level_id, ")
                .append(" CASE WHEN pet.merit_pay_code <> pb.merit_pay_code THEN pb.merit_pay_code ELSE NULL END pbMeritPayCode, ")
                .append(" CASE WHEN pio.person_name <> pb.person_name THEN pb.person_name ELSE NULL END pbName, ")
                .append(" CASE WHEN pio.iden_no <> pb.iden_no THEN pb.iden_no ELSE NULL END pbIdenNo, ")
                .append(" CASE WHEN pet.sala_card_no <> pb.sala_card_no THEN pb.sala_card_no ELSE NULL END pbSalaCardNo , ")
                .append(" CASE WHEN pet.sch_rec_code <> pb.sch_rec_code THEN pb.sch_rec_code ELSE NULL END pbsch_rec_code 	, ")
                .append(" CASE WHEN pet.person_type_code <> pb.person_type_code THEN pb.person_type_code ELSE NULL END pbperson_type_code 	,  ")
                .append(" CASE WHEN pet.person_address <> pb.person_address THEN pb.person_address ELSE NULL END pbperson_address 	, ")
                .append(" CASE WHEN pet.is_auth <> pb.is_auth THEN pb.is_auth ELSE NULL END pbis_auth 	,                       ")
                .append(" CASE WHEN pet.enter_agency_date <> pb.enter_agency_date THEN pb.enter_agency_date ELSE NULL END pbenter_agency_date 	, ")
                .append(" CASE WHEN pet.pos_code <> pb.pos_code THEN pb.pos_code ELSE NULL END pbpos_code 	, ")
                .append(" CASE WHEN pet.gr_code <> pb.gr_code THEN pb.gr_code ELSE NULL END pbgr_code 	,  ")
                .append(" CASE WHEN pet.tec_gr_code <> pb.tec_gr_code THEN pb.tec_gr_code ELSE NULL END pbtec_gr_code 	, ")
                .append(" CASE WHEN pet.sala_gr_code <> pb.sala_gr_code THEN pb.sala_gr_code ELSE NULL END pbsala_gr_code , ")
                .append(" CASE WHEN pet.sala_card_bank <> pb.sala_card_bank THEN pb.sala_card_bank ELSE NULL END pbsala_card_bank , ")
                .append(" CASE WHEN pet.retire_date <> pb.retire_date THEN pb.retire_date ELSE NULL END pbretire_date, ")
                .append(" CASE WHEN pet.person_ide_code <> pb.person_ide_code THEN pb.person_ide_code ELSE NULL END pbperson_ide_code , ")
                .append(" CASE WHEN pet.is_special_post <> pb.is_special_post THEN pb.is_special_post ELSE NULL END pbis_special_post ,")
                .append(" CASE WHEN pet.func_name <> pb.func_name THEN pb.func_name ELSE NULL END pbfunc_name, ")
                .append(" CASE WHEN pet.person_type_id <> pb.person_type_id THEN pb.person_type_id ELSE NULL END pbperson_type_id, ")
                .append(" CASE WHEN pet.post_id <> pb.post_id THEN pb.post_id ELSE NULL END pbpost_id, ")
                .append(" CASE WHEN pet.grade_id <> pb.grade_id THEN pb.grade_id ELSE NULL END pbgrade_id, ")
                .append(" CASE WHEN pet.level_id <> pb.level_id THEN pb.level_id ELSE NULL END pblevel_id ")
                .append(" from ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai ")
                .append(" on ").append(" pet.agency_id = ai.id")
                .append(" left join ").append(TableName.SAL_PERSON_EXT_BEGIN).append(" pb ")
                .append(" on ").append("  pio.id=pb.person_id and pb.person_id=? ")
                .append(" where ").append("  pio.id = ? ");
        Object[] objects=new Object[]{
                personId,personId
        };
        try {
            return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(PersonInfoVO.class), objects);
        } catch (DataAccessException e) {
            return new PersonInfoVO();
        }
    }

    @Override
    public List<PersonExt> getIdAndMeritPayCodeByIdenNo(String agencyid) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(" pet.person_id, pet.merit_pay_code,pio.iden_no ")
                .append(" from ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" where ").append(" pet.agency_id = ? and pio.is_deleted = ? ");
        Object[] params = new Object[]{
                agencyid, SystemDictConstant.IS_FALSE
        };
        try {
            return this.getJdbcTemplate().query(sql.toString(), params, new BeanPropertyRowMapper<>(PersonExt.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    @Override
    public List<String> getIdenNos(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(" iden_no  ")
                .append(" from ").append(TableName.PERSON_INFO)
                .append(" where mof_div_code=? and is_deleted=0 ");
        try {
            return this.getJdbcTemplate().queryForList(sql.toString(),String.class,province);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void physicalDeleteByPersonId(String personId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ")
                .append(TableName.PERSON_INFO)
                .append(" where id = ? and is_deleted= 0 ");
        super.getjdbcTemplate().update(sb.toString(), personId);
    }

    @Override
    public List<PersonExt> getPersonExtByHistory(String month, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                .append("change_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary, is_deleted,mof_div_code, ")
                .append(" person_type_id, post_id, grade_id, level_id,person_name,show_order,phone,personnel_code,func_id,func_name,func_code ")
                .append(" from ")
                .append(TableName.PERSON_HISTORY)
                .append(" where ").append(SysUtils.inSql("agency_id",agencyIds))
                .append(" and salary_month=? ");
        Object[] params = new Object[]{month};
        return this.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper(PersonExt.class),params);
    }


//    @Override
//    public List<PersonInfo> queryPersonInfo(List<String> id) {
//        StringBuilder sql = new StringBuilder();
//        sql.append(" select ")
//                .append("a.person_name," +
//                        "a.dept_id," +
//                        "a.show_order," +
//                        "b.agency_name ")
//                .append(" from ").append(TableName.PERSON_EXT).append(" a ")
//                .append(" left join ").append(TableName.AGENCY_INFO).append(" b ")
//                .append(" on ").append(" a.agency_id = b.id ")
//                .append(" where ")
//                .append(SysUtils.inSql("a.id", id));
//        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonInfo.class));
//    }
}
